package com.wanmait.travel.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.travel.util.DbHelper;
import com.wanmait.travel.vo.Scenic;
import com.wanmait.travel.vo.Tickit;

public class PriceDAO {
	private static PriceDAO priceDAO;
	private PriceDAO() {}
	public  static PriceDAO getDAO() {
		if(priceDAO==null) {
		 priceDAO = new PriceDAO();
		}
		return priceDAO;
	}
   public List<Scenic> findByPrice(double priceO,double priceT)
   {
	   ArrayList<Scenic> scenics= new ArrayList<Scenic>();
	   String sql="SELECT  scenic.* FROM scenic  WHERE scenic.id IN (SELECT distinct scenic_id FROM tickit WHERE price>=? AND price<=?)";
	   Connection conn =DbHelper.getConn();
	   PreparedStatement ps=null;
	   ResultSet rs=null;
	   try {
		ps=conn.prepareStatement(sql);
		ps.setObject(1,priceO);
		ps.setObject(2,priceT);
		rs=ps.executeQuery();
		while(rs.next())
		{
			Scenic scenic=new Scenic();
			scenic.setId(rs.getInt("id"));
			scenic.setName(rs.getString("name"));
			scenic.setLocation(rs.getString("location"));
			scenic.setOpenTime(rs.getString("open_time"));
			scenic.setCloseTime(rs.getString("close_time"));
			scenic.setIntroPic(rs.getString("intro_pic"));
			scenic.setLatitude(rs.getDouble("latitude"));
			scenic.setTen(rs.getDouble("ten"));
			scenics.add(scenic);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   finally {
			DbHelper.close(rs, ps, conn);
		}
	   return scenics;
   }
   
   public ArrayList<Scenic> findByMax()
   {
	   ArrayList<Scenic> scenics= new ArrayList<Scenic>();
	   String sql="SELECT distinct  scenic.* FROM scenic left JOIN tickit ON scenic.id=tickit.scenic_id ORDER BY tickit.price desc";
	   Connection conn =DbHelper.getConn();
	   PreparedStatement ps=null;
	   ResultSet rs=null;
	   try {
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next())
		{
			Scenic scenic=new Scenic();
			scenic.setId(rs.getInt("id"));
			scenic.setName(rs.getString("name"));
			scenic.setLocation(rs.getString("location"));
			scenic.setOpenTime(rs.getString("open_time"));
			scenic.setCloseTime(rs.getString("close_time"));
			scenic.setIntroPic(rs.getString("intro_pic"));
			scenic.setLatitude(rs.getDouble("latitude"));
			scenic.setTen(rs.getDouble("ten"));
			scenics.add(scenic);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   finally {
			DbHelper.close(rs, ps, conn);
		}
	   return scenics;
   }
   public ArrayList<Scenic> findByMin()
   {
	   ArrayList<Scenic> scenics= new ArrayList<Scenic>();
	   String sql="SELECT distinct  scenic.* FROM scenic left JOIN tickit ON scenic.id=tickit.scenic_id ORDER BY tickit.price ";
	   Connection conn =DbHelper.getConn();
	   PreparedStatement ps=null;
	   ResultSet rs=null;
	   try {
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next())
		{
			Scenic scenic=new Scenic();
			scenic.setId(rs.getInt("id"));
			scenic.setName(rs.getString("name"));
			scenic.setLocation(rs.getString("location"));
			scenic.setOpenTime(rs.getString("open_time"));
			scenic.setCloseTime(rs.getString("close_time"));
			scenic.setIntroPic(rs.getString("intro_pic"));
			scenic.setLatitude(rs.getDouble("latitude"));

			scenic.setTen(rs.getDouble("ten"));
			scenics.add(scenic);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   finally {
			DbHelper.close(rs, ps, conn);
		}
	   return scenics;
   }
   
   
   
   
   
   
   
}
